# -*- coding: UTF-8 -*-
# 获取品牌保养件的pid,
# 场景:例如:获取路虎的保养件的pid,然后拿pid去马勒网站上搜索,获取其零件数据
# Python Version: 2.7.1
import xlwt
import pymysql
import sys

# 解决编码问题
reload(sys)
sys.setdefaultencoding('utf-8')

class Get_Pid_Excel(object):

    def __init__(self, file_name):

        # 定义要抽取的品牌的列表
        self.brand_list = [
            # 'land_rover',
            # 'jaguar',
            # 'porsches',  # 0716获取
            'honda'
        ]

        # 定义要抽取的保养项目
        self.item_list = [
            # '更换机油机滤',
            # '更换空调滤芯',
            # '更换空调滤清器滤芯',
            # '更换空气滤清器滤芯',
            # '更换燃油滤清器滤芯',  # 以上为路虎捷豹保时捷0716获取
            '更换变速箱油/滤芯',
            '更换空气滤清器滤芯',
            '更换燃油滤清器',
            '更换空调滤清器滤芯',
            '更换机油滤清器滤芯',
        ]

        self.rows = 0
        self.workbook = xlwt.Workbook(encoding = 'utf-8')
        self.worksheet = self.workbook
        self.file_name = file_name

    def write_xls_title(self, worksheet, label_list):
        i = 0
        for label in label_list:
            worksheet.write(self.rows, i, label=label)
            i += 1

        self.rows += 1  # 写完表头行数+1

    def get_worksheet(self, sheet='sheet1'):
        return self.workbook.add_sheet(sheetname=sheet)

    def save2xls(self, data_dict, worksheet):
        worksheet.write(self.rows, 0, label=data_dict.get('data_brand'))
        worksheet.write(self.rows, 1, label=data_dict.get('data_item'))
        worksheet.write(self.rows, 2, label=data_dict.get('data_pid'))

        self.rows += 1  # 写完一行,行数+1

    def get_curs(self, db_config, type='stream'):
        conn = pymysql.connect(**db_config)
        if type == 'stream':
            return conn.cursor(pymysql.cursors.SSCursor)  # 返回流式游标,查询大量数据时不占用内存(返回数据形式是元组)
        elif type == 'dict':
            return conn.cursor(pymysql.cursors.DictCursor)  # 返回字典形式游标,查询出的数据以字典形式返回
        else:
            raise Exception("cursor type error")

    def run(self):

        worksheet = self.get_worksheet()

        label_list = ['brand','item','pid']  # 定义表头列表
        self.write_xls_title(worksheet=worksheet, label_list=label_list)  # 写表头

        for brand in self.brand_list:
            # mysql配置
            db_config = dict(
                host="192.168.10.166",
                port=3307,
                user="a",
                passwd="110",
                db="%s" % brand,
                charset="utf8")

            curs = self.get_curs(db_config=db_config)

            sql = '''select item,pid from {}_m_part'''.format(brand)  # 定义查询语句,
            curs.execute(sql)

            for data in curs:  # 遍历查询出来的数据
                data_item = data[0]
                if not data_item in self.item_list:
                    continue
                data_pid = data[1]

                # 组装最终数据字典
                data_dict = dict(
                    data_brand=brand,
                    data_item=data_item,
                    data_pid=data_pid
                )
                self.save2xls(data_dict=data_dict, worksheet=worksheet)  # 保存到xls

        self.workbook.save(self.file_name + '.xls')

if __name__ == '__main__':
    file_name = u'0719_pid'
    tools = Get_Pid_Excel(file_name=file_name)
    tools.run()

